﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public class ShoppingHobbyService
    {
        private static String ConServerStr = @" Data Source=bds249611391.my3w.com;Initial Catalog=bds249611391_db;Persist Security Info=True;User ID=bds249611391;Password=hq312453";
        SqlConnection conn = new SqlConnection(ConServerStr);
        



        public bool SearchTable()
        {
            bool tempbool = false;
            string sql = "select * from ShoppingHobby";
            conn.Open();
            SqlCommand com = new SqlCommand(sql,conn);
            SqlDataReader dr = com.ExecuteReader();
            while (dr.Read())
            {
                tempbool = true;
                break;
            }
            dr.Close();
            com = null;
            conn.Close();
            return tempbool;
        }

        #region 创建购物爱好表
        /// <summary>
        /// 创建购物爱好表
        /// </summary>
        public void createShoppingHobby()
        {
            try
            {
                conn.Open();
                string tbna = "ShoppingHobby";
                string sql = " use bds249611391_db"
                    + " create table " + tbna
                    + " ("
                    + " ShoppingHobbyId int not null identity(1,1),"//主键Id
                    + " Explain nvarchar(200) null,"
                    + " ShoppingHobbyName nvarchar(200) not null"//购物爱好说明
                    + " )"
                    + " use bds249611391_db"
                    + " alter table " + tbna
                    + " add constraint PK_ShoppingHobbyId primary key (ShoppingHobbyId)";
                SqlCommand com = new SqlCommand(sql, conn);
                com.ExecuteNonQuery();
                com = null;
                conn.Close();
                Insert(new ShoppingHobby() { Explain="购物爱好", ShoppingHobbyName=string.Empty });
            }
            catch (Exception)
            {

            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="sh"></param>
        /// <returns></returns>
        public int Insert(ShoppingHobby sh)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "insert into ShoppingHobby (Explain,ShoppingHobbyName) values (@Explain,@ShoppingHobbyName)";
            if (sh.Explain == string.Empty)
                sql = "insert into ShoppingHobby (Explain,ShoppingHobbyName) values (default,@ShoppingHobbyName)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@Explain", sh.Explain),
                new SqlParameter("@ShoppingHobbyName", sh.ShoppingHobbyName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="sh"></param>
        /// <returns></returns>
        public int Delete(ShoppingHobby sh)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "delete ShoppingHobby where ShoppingHobbyName=@ShoppingHobbyName";
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql, new SqlParameter("@ShoppingHobbyName", sh.ShoppingHobbyName));
        }
        #endregion

        #region 修改信息
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="sh"></param>
        /// <param name="setStr">修改后的值</param>
        /// <returns></returns>
        public int Set(ShoppingHobby sh,string setStr)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "update ShoppingHobby set ShoppingHobbyName=@setStr where ShoppingHobbyName=@ShoppingHobbyName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@setStr",setStr),
                new SqlParameter("@ShoppingHobbyName",sh.ShoppingHobbyName)
            };
            return SQLHelper.ExecuteNonQuery(conn,System.Data.CommandType.Text,sql,pars);
        }
        #endregion

        #region 查找信息
        /// <summary>
        /// 查找信息
        /// </summary>
        /// <returns></returns>
        public List<ShoppingHobby> SearchAll()
        {
            SqlConnection conn = ConnPoll.GetConn();
            List<ShoppingHobby> lsh = new List<ShoppingHobby>();
            string sql = "select * from ShoppingHobby";
            SqlDataReader dr = SQLHelper.ExecuteReader(conn,System.Data.CommandType.Text,sql);
            while (dr.Read())
            {
                if (dr["ShoppingHobbyName"].ToString() == string.Empty)
                    continue;
                ShoppingHobby s = new ShoppingHobby();
                s.ShoppingHobbyId = Convert.ToInt32(dr["ShoppingHobbyId"]);
                s.ShoppingHobbyName = dr["ShoppingHobbyName"].ToString();
                lsh.Add(s);
            }
            dr.Close();
            return lsh;
        }
        #endregion


        #region 查询标题
        /// <summary>
        /// 查询标题
        /// </summary>
        /// <returns></returns>
        public string SearchTitle()
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select Explain from ShoppingHobby";
            return SQLHelper.ExecuteScalar(conn,System.Data.CommandType.Text,sql).ToString();
        }
        #endregion


        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否已有此内容
        /// </summary>
        /// <param name="sh"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Search(ShoppingHobby sh, string UserName)
        {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) form ShoppingHobby" + UserName+ " where ShoppingHobbyName=@ShoppingHobbyName";
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@ShoppingHobbyName", sh.ShoppingHobbyName)));
        }
        #endregion
    }
}
